#coding:utf-8
__author__ = "hzq"
__date__ = "2011-2-20 13:29:32"

import os
import random
from string import join
import time

import MySQLdb
import MySQLdb.cursors
import libs
import pagelibs


class mysql:

    def __init__(self, dicts, cacheDir="data"):
        self.conn = MySQLdb.connect(host=dicts["host"], user=dicts["user"], passwd=dicts["passwd"], db=dicts["db"], charset="utf8", cursorclass=MySQLdb.cursors.DictCursor)
        self.db = self.conn.cursor()
        self.cacheDir = cacheDir
        
        if os.path.isdir(self.cacheDir) == False:
            os.makedirs(self.cacheDir)

    def  query(self, sql, param=()):
        '''如果参数为tpule 那么批量
        '''

        if type(param) == type(()):
            
           
            return self.db.execute(sql, param)
        else:
            
            return self.db.executemany(sql, param)

    def  query_commit(self, sql, param=()):
        '''
        提交并保存
        '''

        self.query(sql, param)
        return self.conn.commit()

    def getResultOne(self, ** arg):
        sql = arg.pop("sql", [])
        param = arg.pop("param", [])
        sql = sql % param
        self.query(sql)
        return self.db.fetchone()
    def getResultOneCache(self, ** arg):
        times = arg.pop("times", [])
       
        sql = arg.pop("sql", [])
        param = arg.pop("param", [])
        sql = sql % param
        names = libs.md5(sql)
        c = libs.cache(self.cacheDir)
        res = c[names]
        if res == False:
            res = self.getResultOne(sql=sql)
            c.cacheSet(res, names, times)
 
        return res


    def getResultAll(self, ** arg):
        sql = arg.pop("sql", [])
        param = arg.pop("param", [])
        sql = sql % param
        self.query(sql)
        return self.db.fetchall()


    def getResultAllCache(self, ** arg):
        times = arg.pop("times", [])

        sql = arg.pop("sql", [])
        param = arg.pop("param", [])
        sql = sql % param
        names = libs.md5(sql)
        c = libs.cache(self.cacheDir)
        res = c[names]
        if res == False:
            res = self.getResultAll(sql=sql)
            c.cacheSet(res, names, times)

        return res

    def insertData(self, tab, dicts):
        kk = []
        vv = []
        for k in dicts.keys():
            kk.append(k)
            vv.append(self.addslashes(dicts[k]))
       
        ks = "`,`".join(kk)
        ks = '`' + ks + '`'
        vs = "','".join(vv)
        vs = "'" + vs + "'"
        sql = "insert into %s (%s) values(%s)" % (tab, ks, vs)
        
      
        self.query(sql)
        id = self.conn.insert_id()
        self.conn.commit()
        return id

    def updata(self, tab, where, dicts):
        lists = []
        for k in dicts.keys():
            if len(k) > 0:
                lists.append("`%s`='%s'" % (k, self.addslashes(dicts[k])))
        sql = "update %s set %s where %s" % (tab, " , ".join(lists), where)

        return self.query_commit(sql)


    def deleted(self, tab, dicts):
        if type(dicts) == type({}):
            lists = []
            for k in dicts.keys():
                lists.append("`%s`='%s'" % (k, dicts[k]))
            
            where = "where %s" % " and ".join(lists)
        elif type(dicts) == type("a"):
            where = " where " + dicts
        sql = "delete  from %s %s" % (tab, where)
        return self.query(sql)
       
    def getDataByRand(self, tab, num, pk, filds="*"):

        sql = ''
        sql = "select count(*) as n  from " + tab;
        res = self.getResultOne(sql=sql)
        anum = res['n']-num
        limit = random.randint(0, anum)
        sql = "select %s from %s where %s>%d   order by rand() limit %d  " % (filds, tab, pk, anum, num)
       

        return self.getResultAll(sql=sql)


    def  pageSelect(self, field, where, num, nowpage, tpl, tab):
        result = {}
        res = self.getResultOneCache(sql="SELECT COUNT(*)  as n  from  %s     %s " % (tab, where), times=3600)
        pages = pagelibs.myPages(num, nowpage, res["n"], tpl)
        sql = "select %s  from %s   %s  limit  %s , %s" % (field, tab, where, pages.begin, num)
      
        result["lists"] = self.getResultAll(sql=sql)
        result["pagelist"] = pages.showBase("pageslist")
        return result

    def addslashes(self, str):
        return MySQLdb.escape_string(str).replace('%', '%%')

       
   

        